OptionTheoVol

V8 Message Definiton

OptionTheoVol records contain client supplied theoretical volatility surface information resolved at the level if individual strikes. Strike volatilities, prices, greeks and SpiderRock surface volatilites and prices are all available. Values are computed on the fly using fast/accurate calculation methods as records are returned.

METADATA

Attribute	Value
Topic	5030-srse-calculators
MLink Token	ClientTheo
Product	SRTheo
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
okey_at	enum - AssetType	PRI	'None'
okey_ts	enum - TickerSrc	PRI	'None'
okey_tk	VARCHAR(12)	PRI	''
okey_yr	SMALLINT UNSIGNED	PRI, SEC	0
okey_mn	TINYINT UNSIGNED	PRI, SEC	0
okey_dy	TINYINT UNSIGNED	PRI, SEC	0
okey_xx	DOUBLE	PRI	0
okey_cp	enum - CallPut	PRI	'Call'
theoModel	VARCHAR(16)		''	required in where clause theo model must be associated with an accnt that is visible
clientFirmOut	VARCHAR(16)		''
years	FLOAT		0	volatility years to expiration variable time metric SR holiday calendar
ticker_at	enum - AssetType		'None'	underlier ticker
ticker_ts	enum - TickerSrc		'None'	underlier ticker
ticker_tk	VARCHAR(12)	SEC	''	underlier ticker
uprc	FLOAT		0	underlier price
ubid	FLOAT		0	underlier bid
uask	FLOAT		0	underlier ask
obid	FLOAT		0	option market bid
oask	FLOAT		0	option market ask
tvol	FLOAT		0	theo vol
tvolBOpn	FLOAT		0	theo buyopen vol
tvolSOpn	FLOAT		0	theo sellopen vol
tprc	FLOAT		0	theo vol price
tbPrc	FLOAT		0	theo buy open price
tsPrc	FLOAT		0	theo sell open price
tbClsPrc	FLOAT		0	theo buy close price
tsClsPrc	FLOAT		0	theo sell close price
veSlope	FLOAT		0	veSlope dVol dUprc assuming vol xAxis 0 remains constanthedgeDelta de ve 100 veSlope if hedging with this assumption
de	FLOAT		0	implied greeks from theo surface
ga	FLOAT		0
th	FLOAT		0
ve	FLOAT		0
vo	FLOAT		0
va	FLOAT		0
deDecay	FLOAT		0
ro	FLOAT		0
ph	FLOAT		0
sdivB	FLOAT		0	normalized sdiv when buying
sdivS	FLOAT		0	normalized sdiv when selling
xAxis	FLOAT		0	xAxis value depends on xAxis definition in TheoExpSurface record
tvolAtm	FLOAT		0	theo model volatility atm
ivolAtm	FLOAT		0	SR Surface Volatility atm
svol	FLOAT		0	SR Surface Volatility
sprc	FLOAT		0	SR Surface Price
sDe	FLOAT		0	SR Surface Delta
sVe	FLOAT		0	SR Surface Vega
rate	FLOAT		0	SR Rate average interest rate to expiration
sdiv	FLOAT		0	SR SDiv continuous dividendaccretive with discrete dividends
ddiv	FLOAT		0	SR DDiv sum of discrete dividend amounts prior to expiration
err	TINYINT UNSIGNED		0
theoErr	VARCHAR(24)		''
calcErr	VARCHAR(24)		''
theoStatus	enum - TheoStatus		'Hold'
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
okey_tk	1
okey_yr	2
okey_mn	3
okey_dy	4
okey_xx	5
okey_cp	6
okey_at	7
okey_ts	8

SECONDARY INDEX (ExpirationIndex) (Not Unique)

Field	Sequence
okey_yr	1
okey_mn	2
okey_dy	3

SECONDARY INDEX (TickerIndex) (Not Unique)

Field	Sequence
ticker_tk	1

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTheo`.`MsgOptionTheoVol` (
    `okey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `okey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `okey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `okey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_xx` DOUBLE NOT NULL DEFAULT 0,
    `okey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call',
    `theoModel` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'required in where clause (theo model must be associated with an accnt that is visible)',
    `clientFirmOut` VARCHAR(16) NOT NULL DEFAULT '',
    `years` FLOAT NOT NULL DEFAULT 0 COMMENT 'volatility years to expiration (variable time metric; SR holiday calendar)',
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'underlier ticker',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'underlier ticker',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'underlier ticker',
    `uprc` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier price',
    `ubid` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier bid',
    `uask` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier ask',
    `obid` FLOAT NOT NULL DEFAULT 0 COMMENT 'option market bid',
    `oask` FLOAT NOT NULL DEFAULT 0 COMMENT 'option market ask',
    `tvol` FLOAT NOT NULL DEFAULT 0 COMMENT 'theo vol',
    `tvolBOpn` FLOAT NOT NULL DEFAULT 0 COMMENT 'theo buy/open vol',
    `tvolSOpn` FLOAT NOT NULL DEFAULT 0 COMMENT 'theo sell/open vol',
    `tprc` FLOAT NOT NULL DEFAULT 0 COMMENT 'theo vol price',
    `tbPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'theo buy (open) price',
    `tsPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'theo sell (open) price',
    `tbClsPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'theo buy (close) price',
    `tsClsPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'theo sell (close) price',
    `veSlope` FLOAT NOT NULL DEFAULT 0 COMMENT 'veSlope = dVol / dUprc (assuming vol @ xAxis = 0 remains constant);hedgeDelta = (de + ve * 100 * veSlope) if hedging with this assumption',
    `de` FLOAT NOT NULL DEFAULT 0 COMMENT 'implied greeks (from theo surface)',
    `ga` FLOAT NOT NULL DEFAULT 0,
    `th` FLOAT NOT NULL DEFAULT 0,
    `ve` FLOAT NOT NULL DEFAULT 0,
    `vo` FLOAT NOT NULL DEFAULT 0,
    `va` FLOAT NOT NULL DEFAULT 0,
    `deDecay` FLOAT NOT NULL DEFAULT 0,
    `ro` FLOAT NOT NULL DEFAULT 0,
    `ph` FLOAT NOT NULL DEFAULT 0,
    `sdivB` FLOAT NOT NULL DEFAULT 0 COMMENT 'normalized sdiv when buying',
    `sdivS` FLOAT NOT NULL DEFAULT 0 COMMENT 'normalized sdiv when selling',
    `xAxis` FLOAT NOT NULL DEFAULT 0 COMMENT 'xAxis value (depends on xAxis definition in TheoExpSurface record)',
    `tvolAtm` FLOAT NOT NULL DEFAULT 0 COMMENT 'theo model volatility (atm)',
    `ivolAtm` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR Surface Volatility (atm)',
    `svol` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR Surface Volatility',
    `sprc` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR Surface Price',
    `sDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR Surface Delta',
    `sVe` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR Surface Vega',
    `rate` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR Rate (average interest rate to expiration)',
    `sdiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR SDiv (continuous dividend;accretive with discrete dividends)',
    `ddiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR DDiv (sum of discrete dividend amounts prior to expiration)',
    `err` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `theoErr` VARCHAR(24) NOT NULL DEFAULT '',
    `calcErr` VARCHAR(24) NOT NULL DEFAULT '',
    `theoStatus` ENUM('Hold','Auto','Scanner','Markup','CloseOnly') NOT NULL DEFAULT 'Hold',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`okey_tk`,`okey_yr`,`okey_mn`,`okey_dy`,`okey_xx`,`okey_cp`,`okey_at`,`okey_ts`),
    KEY `ExpirationIndex` (`okey_yr`,`okey_mn`,`okey_dy`) USING HASH,
    KEY `TickerIndex` (`ticker_tk`) USING HASH
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='OptionTheoVol records contain client supplied theoretical volatility surface information resolved at the level if individual strikes.  Strike volatilities, prices, greeks and SpiderRock surface volatilites and prices are all available.  Values are computed on the fly using fast/accurate calculation methods as records are returned.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `okey_at`,
    `okey_ts`,
    `okey_tk`,
    `okey_yr`,
    `okey_mn`,
    `okey_dy`,
    `okey_xx`,
    `okey_cp`,
    `theoModel`,
    `clientFirmOut`,
    `years`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `uprc`,
    `ubid`,
    `uask`,
    `obid`,
    `oask`,
    `tvol`,
    `tvolBOpn`,
    `tvolSOpn`,
    `tprc`,
    `tbPrc`,
    `tsPrc`,
    `tbClsPrc`,
    `tsClsPrc`,
    `veSlope`,
    `de`,
    `ga`,
    `th`,
    `ve`,
    `vo`,
    `va`,
    `deDecay`,
    `ro`,
    `ph`,
    `sdivB`,
    `sdivS`,
    `xAxis`,
    `tvolAtm`,
    `ivolAtm`,
    `svol`,
    `sprc`,
    `sDe`,
    `sVe`,
    `rate`,
    `sdiv`,
    `ddiv`,
    `err`,
    `theoErr`,
    `calcErr`,
    `theoStatus`,
    `timestamp`
FROM `SRTheo`.`MsgOptionTheoVol`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `okey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `okey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `okey_tk` = 'Example_okey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `okey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `okey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `okey_cp` = 'Call';

Doc Columns Query

SELECT * FROM SRTheo.doccolumns WHERE TABLE_NAME='OptionTheoVol' ORDER BY ordinal_position ASC;